const db = require('../db/connMysql');

const censusDao = {
    /*
     * 获取某个学生测评记录限制在院系内
     */
    async getAssessRecordsByStudentIdOfFaculty(uid, studentId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               a.assessRecordId as assessRecordId,
                               s.cardId as cardId,
                               s.name as studentName,
                               DATE_FORMAT(a.assessTime, '%Y-%m-%d %H:%i:%s') AS assessTime,
                               a.testName as testName,
                               a.score as score,
                               a.level as level
                           from AssessRecords as a
                           left join Students as s on a.studentId = s.studentId
                           left join Classes as c on s.classId = c.classId
                           where a.studentId = ? and c.facultyId = (select facultyId from Users where uid = ?)`;
                connection.query(sql, [studentId, uid], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },

    /*
     * 获取某个学生测评记录
     */
    async getAssessRecordsByStudentId(uid, studentId) {
        let result = await new Promise((resolve, reject) => {
            db.getConnection((err, connection) => {
                if (err) {
                    // 连不上数据库
                    connection.release();
                    reject(err);
                    return ;
                }
                let sql = `select
                               a.assessRecordId as assessRecordId,
                               s.cardId as cardId,
                               s.name as studentName,
                               DATE_FORMAT(a.assessTime, '%Y-%m-%d %H:%i:%s') AS assessTime,
                               a.testName as testName,
                               a.score as score,
                               a.level as level
                           from AssessRecords as a
                           left join Students as s on a.studentId = s.studentId
                           where a.studentId = ?`;
                connection.query(sql, [studentId], function (err, result) {
                    if (err) {
                        // 连上数据库之后，执行sql时发生错误
                        connection.release();
                        reject(err);
                        return ;
                    }
                    connection.release();
                    resolve(result);
                });
            });
        });
        return result;
    },
};

module.exports = censusDao;
